-- @owner: wang-tianjing1
-- @date: 2024/8/19
-- @testpoint:指定触发器的触发频率为for each statement，使用show triggers查询

@conn PrimaryDbBmode;
--step1:创建源表及触发表 expect:成功
drop table if exists test_trigger_src_tbl;
drop table if exists test_trigger_des_tbl;
create table test_trigger_src_tbl(id1 int, id2 int, id3 int);
create table test_trigger_des_tbl(id1 int, id2 int, id3 int);

--step2:创建触发器函数 expect:成功
create or replace function tri_insert_func01() returns trigger as
$$
declare
begin
insert into test_trigger_des_tbl values(7,8,9);
return new;
end
$$
language plpgsql;
/

--step3:创建insert触发器 expect:成功
create trigger insert_trigger01 after insert on test_trigger_src_tbl
 for each statement execute procedure tri_insert_func01();
 /

 --step4:执行insert触发事件并检查触发结果 expect:成功
 insert into test_trigger_src_tbl values(100,200,300);
 insert into test_trigger_src_tbl values(400,500,600);
 select * from test_trigger_src_tbl;
 select * from test_trigger_des_tbl;

 --step5:查询触发器 expect:成功
 show triggers where "Trigger"  like 'insert_trigger01';
 show triggers where "Trigger"  like 'insert_trigger01' and "Event" like 'INSERT';

 --step6:清理环境 expect:成功
 drop trigger insert_trigger01 on test_trigger_src_tbl;
 drop function tri_insert_func01() cascade;
 drop table test_trigger_src_tbl;
 drop table test_trigger_des_tbl;